//
// Created by henry on 2025/11/7.
//

#include "database.h"
//定义数据库句柄
sqlite3 * db;

//打开数据库
int open_db(){
    int rc = sqlite3_open("canteen.db",&db);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"open_db:创建数据库失败:%s\n", sqlite3_errmsg(db));
        return -1;
    }
    printf("open_db:创建数据库成功!\n");
    return 0;
}
//初始数据库，建表
int init_db(){
    //打开数据库
    if(open_db()!=SQLITE_OK){
        return -1;
    }
    if(create_stu_user()!=SQLITE_OK){
        return -1;
    }
    //建表
    if(create_canteen_menu()!=SQLITE_OK){
        return -1;
    }

    if(create_admin_user()!=SQLITE_OK){
        return -1;
    }

    if(canteen_order_main()!=SQLITE_OK){
        return -1;
    }


    if(canteen_order_item()!=SQLITE_OK){
        return -1;
    }


//    if(create_order()!=SQLITE_OK){
//        return -1;
//    }
    printf("init_db:数据库初始化完成!\n");
    return 0;
}

//创建学生用户信息表
int create_stu_user(){
//    const char * drop =
//            "drop table stu_user;";
    //定义建表语句
    const char * sql =
            "drop table if EXISTS stu_user;"
            "create table if not exists stu_user"
            "("
            "   id integer primary key,"
            "   username text,"
            "   balance text,"
            "   number text"
            ");";
            "insert into stu_user"
            "(username,balance,number)"
            "values"
            "('张三','10000','1001');";

    //执行sql
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_stu_user:建表出错:%s\n",errmsg);
        return -1;
    }
    printf("create_stu_user:建表成功!\n");
    return 0;
}
//创建食堂菜单表
int create_canteen_menu(){
    const char * sql =
            "drop table if EXISTS canteen_menu;"
            "create table if not exists canteen_menu"
            "("
            "   id integer primary key AUTOINCREMENT,"
            "   menu_name text not null,"
            "   menu_category text,"
            "   menu_money integer,"
            "   menu_number integer"
            ");";
            "insert into canteen_menu"
            "(menu_name,menu_category,menu_money,menu_number)"
            "values"
            "('九转大肠','鲁菜',21,100),('红烧狮子头','鲁菜',20,50);";
    //执行sql
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_canteen_menu:建表出错:%s\n",errmsg);
        return -1;
    }
    printf("create_canteen_menu:建表成功!\n");
    return 0;
}

int canteen_order_main(){
    const char *sql_main =
            "drop table if EXISTS canteen_order_main;"
            "CREATE TABLE IF NOT EXISTS canteen_order_main ("
            "order_id INTEGER PRIMARY KEY AUTOINCREMENT, "  // 自增主键，对应代码中的订单ID
            "stu_user_id INTEGER NOT NULL, "                // 学生ID（关联stu_user表）
            "total_amount REAL NOT NULL"                    // 总金额（浮点型，匹配代码中的double）
            ");";

    char *err_msg = NULL;
    int rc = sqlite3_exec(db, sql_main, NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建canteen_order_main表失败: %s\n", err_msg);
        sqlite3_free(err_msg);
        return -1;
    }
    return 0;
}

 // 2. 创建订单项明细表 canteen_order_item
int canteen_order_item(){
    const char *sql_item =
            "drop table if EXISTS canteen_order_item;"
            "CREATE TABLE IF NOT EXISTS canteen_order_item ("
            "item_id INTEGER PRIMARY KEY AUTOINCREMENT, "    // 订单项自增ID
            "order_id INTEGER NOT NULL, "                    // 关联订单主表的order_id
            "canteen_menu_id INTEGER NOT NULL, "             // 菜品ID（关联canteen_menu表）
            "quantity INTEGER NOT NULL, "                    // 购买数量
            "unit_price REAL NOT NULL, "                     // 单价（浮点型）
            "FOREIGN KEY(order_id) REFERENCES canteen_order_main(order_id), "  // 外键约束（可选）
            "FOREIGN KEY(canteen_menu_id) REFERENCES canteen_menu(id)"         // 外键约束（可选）
            ");";
        char *err_msg = NULL;
   int rc = sqlite3_exec(db, sql_item, NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建canteen_order_item表失败: %s\n", err_msg);
        sqlite3_free(err_msg);
        return -1;
    }
     return 0;
 }
//创建管理员账号表
int create_admin_user(){
    //定义建表语句
    const char * sql =
           "drop table if EXISTS admin_user;"
            "create table if not exists admin_user"
            "("
            "   id integer primary key,"
            "   account text,"
            "   password text,"
            "   status text default 0"
            ");"
            "insert into admin_user"
            "(account,password)"
            "values"
            "('admin','admin');";
    //执行sql
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_admin_user:建表出错:%s\n",errmsg);
        return -1;
    }
    printf("create_admin_user:建表成功!\n");
    return 0;
}


//学生信息表查询
int query_stu_user(const char *sql) {
    if (db == NULL) {
        fprintf(stderr, "数据库连接未初始化！\n");
        return -1;
    }
    printf("进入查询状态\n");
    sqlite3_stmt *stmt = NULL;
    int rc;

    // 1. 预处理 SQL 语句
    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "预处理查询 SQL 失败：%s\n", sqlite3_errmsg(db));
        return -1;
    }

    // 2. 遍历查询结果
    printf("查询结果：\n");
    printf("+----+----------+---------+----------+\n");
    printf("| ID | 用户名   | 余额    | 学号     |\n");
    printf("+----+----------+---------+----------+\n");

    int row_count = 0;
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        // 检查返回的列数是否符合预期
        int col_count = sqlite3_data_count(stmt);
        if (col_count < 4) {
            fprintf(stderr, "警告：查询结果列数不足 4 列，跳过此行。\n");
            continue;
        }

        int id = sqlite3_column_int(stmt, 0);
        const char *username = (const char *)sqlite3_column_text(stmt, 1);
        const char *balance = (const char *)sqlite3_column_text(stmt, 2);
        const char *number = (const char *)sqlite3_column_text(stmt, 3);

        // 打印结果，对所有字符串都进行 NULL 检查
        printf("| %2d | %8s | %8s | %8s |\n",
               id,
               username ? username : "NULL",
               balance ? balance : "NULL",  // <-- 关键修复点
               number ? number : "NULL");

        row_count++;
    }

    // 检查 sqlite3_step 是否因为错误而退出
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "查询数据失败：%s\n", sqlite3_errmsg(db));
        sqlite3_finalize(stmt); // 不要忘记释放资源
        return -1;
    }

    printf("+----+----------+---------+----------+\n");
    printf("查询完成，共 %d 条记录\n", row_count);

    // 3. 释放预处理语句资源
    sqlite3_finalize(stmt);
    return 0;
}

    int order_delete(int order_id) {
        // 定义删除的SQL语句
        const char *sql = "DELETE FROM canteen_order WHERE order_id = ?;";  // 假设订单表有订单ID字段order_id

        // 定义预编译语句句柄
        sqlite3_stmt *stmt;

        // 对SQL语句进行预编译
        int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
        if (rc != SQLITE_OK) {
            fprintf(stderr, "预编译出错了:%s\n", sqlite3_errmsg(db));
            return -1;
        }

        // 绑定参数
        sqlite3_bind_int(stmt, 1, order_id);  // 绑定订单ID

        // 执行删除操作
        rc = sqlite3_step(stmt);
        if (rc != SQLITE_DONE) {
            fprintf(stderr, "删除失败! 错误代码: %d\n", rc);
            sqlite3_finalize(stmt);
            sqlite3_close(db);
            return -1;
        }

        return 0;
    }

//关闭数据库
    int close_db() {
        if (db != NULL) {
            sqlite3_close(db);
            db = NULL;
        }
        printf("close_db:关闭数据库成功!\n");
        return 0;
    }
